package manager;

/**
 * @author huangjm.2012, mohameds.2012, jaslin.wee.2012, huiwensong.2012, sheryl.tay.2012
 * 
 */

import java.sql.SQLException;
import java.util.ArrayList;

import manager.database.DatabaseMgr;
import manager.entitycreator.CourseCreator;
import manager.entitycreator.StudentCreator;
import entity.Course;
import entity.Student;

public class CompletedCourseMgr {

	private DatabaseMgr dbMgr = new DatabaseMgr();
	private CourseCreator courseCreator = new CourseCreator();
	private StudentCreator studentCreator = new StudentCreator();

	public boolean addCompletedCourseForStudent(String courseCode, String userId) {

		String sql = "insert into completed_course (completed_course_userId, completed_course_courseCode) "
				+ " values (?, ?)";

		int numRowsAffected = dbMgr.executeUpdate(sql, userId, courseCode);
		
		return numRowsAffected > 0;
	}
	
	public int[] addMultipleCompletedCourseForStudent(ArrayList<Course> completedCourses, String userId) {
		String sql = "insert into completed_course "
				+ " (completed_course_userId, completed_course_courseCode) "
				+ " values (?,?)";

		int[] numRowsAffected = { 0 };
		try {
			dbMgr.open();

			dbMgr.setStatement(sql);
			for (Course eachCompletedCourse: completedCourses) {
				dbMgr.addBatch(userId , eachCompletedCourse.getCourseCode());
			}
			numRowsAffected = dbMgr.executeBatch();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbMgr.close();
		}
		return numRowsAffected;
	}

	public void reset() {
		dbMgr.executeUpdate("delete from completed_course");
	}

	public ArrayList<Course> retrieveAllCompletedCourses() {

		String sql = "select * from completed_course_with_course_student order by completed_course_courseCode, completed_course_userId";

		return dbMgr.retrieveMultipleEntitiesForQuery(courseCreator, sql);
	}

	public ArrayList<Course> retrieveCompletedCoursesForStudent(String userId) {

		String sql = "select * from completed_course_with_course_student "
				+ " where completed_course_userId =? ";

		return dbMgr.retrieveMultipleEntitiesForQuery(courseCreator, sql,
				userId);
	}

	public ArrayList<Student> retrieveStudentsWhoCompletedCourse(
			String courseCode) {

		String sql = "select * from completed_course_with_course_student "
				+ " where completed_course_courseCode=? order by completed_course_courseCode, completed_course_userId";

		return dbMgr.retrieveMultipleEntitiesForQuery(studentCreator, sql,
				courseCode);
	}

}
